var express = require('express');
var router = express.Router();
var {connection, customQuery} = require('../mysql/index')


// 获取题目
// INSERT INTO `front`.`topic` (`id`, `title`, `type`, `keyword`, `result`, `importance`, `keywordRadio`) VALUES (NULL, NULL, NULL, NULL, NULL, NULL, NULL);

// 新增题目
router.post('/add', function(req, res, next) {
    // Extract fields from the request body
    const { title, type, keyword, result, importance, keywordRadio } = req.body;
    const typeSql = `SELECT name FROM learn_type WHERE id = '${type}'`;
    let typeName;
    customQuery(typeSql).then(results => {
        typeName = results[0].name;
        // Construct the SQL query
        const sql = `INSERT INTO topic (title, type, keyword, result, importance, keywordRadio, typeName) VALUES ('${title}', '${type}', '${keyword}', '${result}', '${importance}', '${keywordRadio}', '${typeName}')`;
        // Execute the query
        customQuery(sql).then(results => {
            return res.status(200).send({ code: 200, msg: 'Data added successfully', list: results });
        })
    })
 
});
// 更新题目
router.post('/update', function(req, res, next) {
    // Extract fields from the request body
    const { id, title, type, keyword, result, importance, keywordRadio } = req.body;
    console.log(' id, title, type, keyword, result, importance, keywordRadio', id, title, type, keyword, result, importance, keywordRadio)
    const typeSql = `SELECT name FROM learn_type WHERE id = '${type}'`;
    console.log('typeSql',typeSql)
    let typeName;
    customQuery(typeSql).then(results => {
        console.log('results',results)
        typeName = results[0].name;
        // Construct the SQL query
        const sql = `UPDATE topic SET title = '${title}', type = '${type}', keyword = '${keyword}', result = '${result}', importance = '${importance}', keywordRadio = '${keywordRadio}', typeName = '${typeName}' WHERE id = ${id}`;
        // Execute the query
        customQuery(sql).then(results => {
            return res.status(200).send({ code: 200, msg: 'Data updated successfully', list: results });
        })
    })
});


// 获取题目
router.get('/', function(req, res, next) {
    // Extract 'type', 'limit', 'page', 'passtopic', and 'random' from the request query
    console.log(req.query)
    const { type, limit = 10, page = 1, passtopic = [], random = false } = req.query;
    // Construct the SQL query
    let sql = `SELECT * FROM topic`;
    console.log(type)
    if (type) {
        sql += ` WHERE type = '${type}'`;
    }
    if (passtopic.length > 0) {
        const passtopicIds = passtopic.join(',');
        sql += type ? ` AND id NOT IN (${passtopicIds})` : ` WHERE id NOT IN (${passtopicIds})`;
    }
    if (random) {
        sql += ' ORDER BY RAND()';
    }
    sql += ` LIMIT ${limit} OFFSET ${(page - 1) * limit}`;

    // Execute the query
    customQuery(sql).then(results => {
        // Construct the SQL query to get total count
        let countSql = `SELECT COUNT(*) as total FROM topic`;
        if (type) {
            countSql += ` WHERE type = '${type}'`;
        }
        if (passtopic.length > 0) {
            const passtopicIds = passtopic.join(',');
            countSql += type ? ` AND id NOT IN (${passtopicIds})` : ` WHERE id NOT IN (${passtopicIds})`;
        }
        // Execute the count query
        customQuery(countSql).then(countResults => {
            let totalSql = `SELECT COUNT(*) as total FROM topic`;
            customQuery(totalSql).then(totalRes => {
                console.log(totalRes)
                return res.status(200).send({ code: 200, msg: 'Data fetched successfully',data:{ list: results, total: countResults[0].total,allTotal: totalRes[0].total} });
            })
        })
    })
});

// 删除题目
router.post('/delete', function(req, res, next) {
    // Extract 'id' from the request body
    const { id } = req.body;

    // Construct the SQL query
    const sql = `DELETE FROM topic WHERE id = ${id}`;

    // Execute the query
    customQuery(sql).then(results => {
        return res.status(200).send({ code: 200, msg: '删除成功', list: results });
    })
});



module.exports = router;
